﻿using LinqModel.Data;
using LinqModel.Models;
using Microsoft.EntityFrameworkCore;

namespace LinqModel.Servcices
{
    public class UserService
    {
        private readonly ApplicationDbContext _context;

        public UserService(ApplicationDbContext context)
        {
            _context = context;
        }

        // 1. 多表联接查询
        public async Task<IEnumerable<object>> GetUsersWithOrdersAsync()
        {
            return await _context.Users
                .Join(_context.Orders,
                    user => user.Id,
                    order => order.UserId,
                    (user, order) => new
                    {
                        UserId = user.Id,
                        Username = user.Username,
                        OrderId = order.Id,
                        OrderAmount = order.Amount,
                        OrderDate = order.OrderDate
                    })
                .OrderBy(x => x.Username)
                .ThenByDescending(x => x.OrderDate)
                .ToListAsync();
        }

        // 2. 分组统计
        public async Task<IEnumerable<object>> GetUsersGroupedByAgeAsync()
        {
            return await _context.Users
                .GroupBy(u => u.Age / 10 * 10) // 按10岁分组
                .Select(g => new
                {
                    AgeGroup = $"{g.Key}-{g.Key + 9}岁",
                    Count = g.Count(),
                    AverageAge = g.Average(u => u.Age),
                    Users = g.Select(u => u.Username).ToList()
                })
                .OrderBy(g => g.AgeGroup)
                .ToListAsync();
        }

        // 3. 分页查询
        public async Task<(IEnumerable<User> Users, int TotalCount)> GetUsersPagedAsync(int pageNumber, int pageSize)
        {
            var query = _context.Users.Where(u => u.IsActive);

            var totalCount = await query.CountAsync();
            var users = await query
                .OrderBy(u => u.Username)
                .Skip((pageNumber - 1) * pageSize)
                .Take(pageSize)
                .ToListAsync();

            return (users, totalCount);
        }

        // 4. 条件动态查询
        public async Task<IEnumerable<User>> SearchUsersAsync(string searchTerm, int? minAge, int? maxAge)
        {
            var query = _context.Users.AsQueryable();

            if (!string.IsNullOrEmpty(searchTerm))
            {
                query = query.Where(u =>
                    u.Username.Contains(searchTerm) ||
                    u.Email.Contains(searchTerm));
            }

            if (minAge.HasValue)
            {
                query = query.Where(u => u.Age >= minAge.Value);
            }

            if (maxAge.HasValue)
            {
                query = query.Where(u => u.Age <= maxAge.Value);
            }

            return await query.OrderBy(u => u.Username).ToListAsync();
        }

    }
}
